const { getDbPool } = require('../infra/dbClient');
const db = getDbPool();
const { getLogger } = require('../logger');
const logger = getLogger('models.tokenModel');

class TokenModel {
    // 创建访问令牌
    static async createAccessToken(params) {
        const { user_id, accessToken, expiresAt, session_id } = params;
        
        const sql = `
            INSERT INTO tokens (user_id, token, token_type, expires_at, session_id)
            VALUES (?, ?, 'access', ?, ?)
        `;

        try {
            const [result] = await db.execute(sql, [user_id, accessToken, expiresAt, session_id]);
            return result.insertId;
        } catch (error) {
            logger.error('创建访问令牌失败', { error });
            throw error;
        }
    }

    // 创建刷新令牌
    static async createRefreshToken(params) {
        const { user_id, refreshToken, expiresAt, session_id } = params;

        const sql = `
            INSERT INTO tokens (user_id, token, token_type, expires_at, session_id)
            VALUES (?, ?, 'refresh', ?, ?)
        `;

        try {
            const [result] = await db.execute(sql, [user_id, refreshToken, expiresAt, session_id]);
            return result.insertId;
        } catch (error) {
            logger.error('创建刷新令牌失败', { error });
            throw error;
        }
    }

    // 创建令牌
    static async createToken(params) {
        const { user_id, token, token_type, expiresAt, session_id } = params;

        const sql = `
            INSERT INTO tokens (user_id, token, token_type, expires_at, session_id)
            VALUES (?, ?, ?, ?, ?)
        `;

        try {
            const [result] = await db.execute(sql, [user_id, token, token_type, expiresAt, session_id]);
            return result.insertId;
        } catch (error) {
            logger.error('创建令牌失败', { error });
            throw error;
        }
    }

    // 根据ID获取令牌
    static async findById(id) {
        const sql = `
            SELECT 
                t.id,
                t.user_id,
                t.token,
                t.token_type,
                t.expires_at,
                t.is_revoked,
                t.created_at,
                t.updated_at,
                t.session_id,
                u.username,
                u.name
            FROM tokens t
            LEFT JOIN user u ON t.user_id = u.id AND u.is_delete = 0
            WHERE t.id = ?
        `;

        try {
            const [rows] = await db.execute(sql, [id]);
            return rows[0] || null;
        } catch (error) {
            logger.error('根据ID获取令牌失败', { error });
            throw error;
        }
    }

    // 根据令牌值获取令牌信息
    static async findByToken(token) {
        const sql = `
            SELECT 
                t.id,
                t.user_id,
                t.token,
                t.token_type,
                t.expires_at,
                t.is_revoked,
                t.created_at,
                t.updated_at,
                t.session_id,
                u.username,
                u.name
            FROM tokens t
            LEFT JOIN user u ON t.user_id = u.id AND u.is_delete = 0
            WHERE t.token = ?
        `;

        try {
            const [rows] = await db.execute(sql, [token]);
            return rows[0] || null;
        } catch (error) {
            logger.error('根据令牌获取信息失败', { error });
            throw error;
        }
    }

    // 获取用户的所有令牌
    static async getTokensByUserId(userId, page = 1, limit = 10, filters = {}) {
        const offset = (page - 1) * limit;
        let whereClause = 'WHERE t.user_id = ?';
        let params = [userId];

        // 构建过滤条件
        if (filters.token_type) {
            whereClause += ' AND t.token_type = ?';
            params.push(filters.token_type);
        }
        if (filters.is_revoked !== undefined) {
            whereClause += ' AND t.is_revoked = ?';
            params.push(filters.is_revoked);
        }
        if (filters.is_expired !== undefined) {
            if (filters.is_expired) {
                whereClause += ' AND t.expires_at < NOW()';
            } else {
                whereClause += ' AND t.expires_at > NOW()';
            }
        }
        if (filters.session_id) {
            whereClause += ' AND t.session_id = ?';
            params.push(filters.session_id);
        }

        const sql = `
            SELECT 
                t.id,
                t.user_id,
                t.token,
                t.token_type,
                t.expires_at,
                t.is_revoked,
                t.created_at,
                t.updated_at,
                t.session_id,
                u.username,
                u.name,
                CASE 
                    WHEN t.expires_at < NOW() THEN 1 
                    ELSE 0 
                END as is_expired
            FROM tokens t
            LEFT JOIN user u ON t.user_id = u.id AND u.is_delete = 0
            ${whereClause}
            ORDER BY t.created_at DESC
            LIMIT ${db.escape(limit)} OFFSET ${db.escape(offset)}
        `;

        try {
            const [rows] = await db.execute(sql, params);
            return rows;
        } catch (error) {
            logger.error('获取用户令牌失败', { error });
            throw error;
        }
    }

    // 获取用户令牌总数
    static async getTokenCountByUserId(userId, filters = {}) {
        let whereClause = 'WHERE t.user_id = ?';
        let params = [userId];

        // 构建过滤条件
        if (filters.token_type) {
            whereClause += ' AND t.token_type = ?';
            params.push(filters.token_type);
        }
        if (filters.is_revoked !== undefined) {
            whereClause += ' AND t.is_revoked = ?';
            params.push(filters.is_revoked);
        }
        if (filters.is_expired !== undefined) {
            if (filters.is_expired) {
                whereClause += ' AND t.expires_at < NOW()';
            } else {
                whereClause += ' AND t.expires_at > NOW()';
            }
        }
        if (filters.session_id) {
            whereClause += ' AND t.session_id = ?';
            params.push(filters.session_id);
        }

        const sql = `SELECT COUNT(*) as total FROM tokens t ${whereClause}`;

        try {
            const [rows] = await db.execute(sql, params);
            return rows[0].total;
        } catch (error) {
            logger.error('获取用户令牌总数失败', { error });
            throw error;
        }
    }

    // 检查令牌是否有效
    static async checkTokenValid(token, token_type) {
        
        const sql = `
            SELECT 
                t.id,
                t.user_id,
                t.token,
                t.token_type,
                t.expires_at,
                t.is_revoked,
                t.session_id,
                u.username,
                u.name,
                u.status as user_status
            FROM tokens t
            INNER JOIN user u ON t.user_id = u.id AND u.is_delete = 0
            WHERE t.token = ? AND t.token_type = ? 
            AND t.is_revoked = 0 AND t.expires_at > NOW()
            AND u.status = 0
        `;

        try {
            const [rows] = await db.execute(sql, [token, token_type]);
            return rows;
        } catch (error) {
            logger.error('检查令牌有效性失败', { error });
            throw error;
        }
    }
    

    // 撤销令牌
    static async revokeToken(token) {
        const sql = `
            UPDATE tokens 
            SET is_revoked = 1, updated_at = NOW()
            WHERE token = ?
        `;

        try {
            const [result] = await db.execute(sql, [token]);
            return result.affectedRows;
        } catch (error) {
            logger.error('撤销令牌失败', { error });
            throw error;
        }
    }

    // 撤销用户的所有令牌
    static async revokeUserTokens(userId) {
        const sql = `
            UPDATE tokens 
            SET is_revoked = 1, updated_at = NOW()
            WHERE user_id = ? AND is_revoked = 0
        `;

        try {
            const [result] = await db.execute(sql, [userId]);
            return result.affectedRows;
        } catch (error) {
            logger.error('撤销用户所有令牌失败', { error });
            throw error;
        }
    }

    // 撤销会话的所有令牌
    static async revokeSessionTokens(sessionId) {
        const sql = `
            UPDATE tokens 
            SET is_revoked = 1, updated_at = NOW()
            WHERE session_id = ? AND is_revoked = 0
        `;

        try {
            const [result] = await db.execute(sql, [sessionId]);
            return result.affectedRows;
        } catch (error) {
            logger.error('撤销会话所有令牌失败', { error });
            throw error;
        }
    }

    // 撤销特定类型的令牌
    static async revokeTokensByType(userId, token_type) {
        const sql = `
            UPDATE tokens 
            SET is_revoked = 1, updated_at = NOW()
            WHERE user_id = ? AND token_type = ? AND is_revoked = 0
        `;

        try {
            const [result] = await db.execute(sql, [userId, token_type]);
            return result.affectedRows;
        } catch (error) {
            logger.error('撤销特定类型令牌失败', { error });
            throw error;
        }
    }

    // 根据会话ID撤销令牌
    static async revokeTokensBySessionId(sessionId) {
        const sql = `
            UPDATE tokens 
            SET is_revoked = 1, updated_at = NOW()
            WHERE session_id = ? AND is_revoked = 0
        `;

        try {
            const [result] = await db.execute(sql, [sessionId]);
            return result.affectedRows;
        } catch (error) {
            logger.error('根据会话ID撤销令牌失败', { error });
            throw error;
        }
    }

    // 根据会话ID获取令牌
    static async getTokensBySessionId(sessionId) {
        const sql = `
            SELECT 
                t.id,
                t.user_id,
                t.token,
                t.token_type,
                t.expires_at,
                t.is_revoked,
                t.created_at,
                t.updated_at,
                t.session_id,
                u.username,
                u.name,
                CASE 
                    WHEN t.expires_at < NOW() THEN 1 
                    ELSE 0 
                END as is_expired
            FROM tokens t
            LEFT JOIN user u ON t.user_id = u.id AND u.is_delete = 0
            WHERE t.session_id = ?
            ORDER BY t.created_at DESC
        `;

        try {
            const [rows] = await db.execute(sql, [sessionId]);
            return rows;
        } catch (error) {
            logger.error('根据会话ID获取令牌失败', { error });
            throw error;
        }
    }

    // 更新令牌会话ID
    static async updateTokenSessionId(tokenId, sessionId) {
        const sql = `
            UPDATE tokens 
            SET session_id = ?, updated_at = NOW()
            WHERE id = ?
        `;

        try {
            const [result] = await db.execute(sql, [sessionId, tokenId]);
            return result.affectedRows;
        } catch (error) {
            logger.error('更新令牌会话ID失败', { error });
            throw error;
        }
    }

    // 获取活跃令牌统计（按会话）
    static async getActiveTokensBySession(userId) {
        const sql = `
            SELECT 
                t.session_id,
                COUNT(*) as token_count,
                COUNT(CASE WHEN t.token_type = 'access' THEN 1 END) as access_tokens,
                COUNT(CASE WHEN t.token_type = 'refresh' THEN 1 END) as refresh_tokens,
                MIN(t.created_at) as first_token_time,
                MAX(t.updated_at) as last_active_time
            FROM tokens t
            WHERE t.user_id = ? AND t.is_revoked = 0 AND t.expires_at > NOW()
            GROUP BY t.session_id
            ORDER BY first_token_time DESC
        `;

        try {
            const [rows] = await db.execute(sql, [userId]);
            return rows;
        } catch (error) {
            logger.error('获取活跃令牌统计失败', { error });
            throw error;
        }
    }

    // 清理过期令牌
    static async cleanExpiredTokens() {
        const sql = `
            DELETE FROM tokens 
            WHERE expires_at < NOW() OR is_revoked = 1
        `;

        try {
            const [result] = await db.execute(sql);
            return result.affectedRows;
        } catch (error) {
            logger.error('清理过期令牌失败', { error });
            throw error;
        }
    }

    // 获取用户的有效令牌数量
    static async getUserActiveTokenCount(userId) {
        const sql = `
            SELECT COUNT(*) as count 
            FROM tokens 
            WHERE user_id = ? AND is_revoked = 0 AND expires_at > NOW()
        `;

        try {
            const [rows] = await db.execute(sql, [userId]);
            return rows[0].count;
        } catch (error) {
            logger.error('获取用户有效令牌数量失败', { error });
            throw error;
        }
    }

    // 获取令牌统计信息
    static async getTokenStats() {
        const sql = `
            SELECT 
                COUNT(*) as total_tokens,
                COUNT(CASE WHEN token_type = 'access' THEN 1 END) as access_tokens,
                COUNT(CASE WHEN token_type = 'refresh' THEN 1 END) as refresh_tokens,
                COUNT(CASE WHEN is_revoked = 0 THEN 1 END) as active_tokens,
                COUNT(CASE WHEN is_revoked = 1 THEN 1 END) as revoked_tokens,
                COUNT(CASE WHEN expires_at > NOW() THEN 1 END) as valid_tokens,
                COUNT(CASE WHEN expires_at < NOW() THEN 1 END) as expired_tokens,
                COUNT(DISTINCT user_id) as users_with_tokens
            FROM tokens
        `;

        try {
            const [rows] = await db.execute(sql);
            return rows[0];
        } catch (error) {
            logger.error('获取令牌统计信息失败', { error });
            throw error;
        }
    }

    // 获取活跃会话统计
    static async getActiveSessionStats() {
        const sql = `
            SELECT 
                DATE(t.created_at) as date,
                COUNT(*) as total_sessions,
                COUNT(DISTINCT t.user_id) as unique_users,
                COUNT(CASE WHEN t.token_type = 'access' THEN 1 END) as access_sessions,
                COUNT(CASE WHEN t.token_type = 'refresh' THEN 1 END) as refresh_sessions
            FROM tokens t
            WHERE t.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
            AND t.is_revoked = 0 AND t.expires_at > NOW()
            GROUP BY DATE(t.created_at)
            ORDER BY date DESC
        `;

        try {
            const [rows] = await db.execute(sql);
            return rows;
        } catch (error) {
            logger.error('获取活跃会话统计失败', { error });
            throw error;
        }
    }

    // 健康检查
    static async healthCheck() {
        try {
            const [rows] = await db.execute('SELECT 1');
            return true;
        } catch (error) {
            logger.error('令牌模型健康检查失败', { error });
            return false;
        }
    }

    // 更新令牌过期时间
    static async updateTokenExpiry(token, newExpiryTime) {
        const sql = `
            UPDATE tokens 
            SET expires_at = ?, updated_at = NOW()
            WHERE token = ? AND is_revoked = 0
        `;

        try {
            const [result] = await db.execute(sql, [newExpiryTime, token]);
            return result.affectedRows;
        } catch (error) {
            logger.error('更新令牌过期时间失败', { error });
            throw error;
        }
    }

    // 批量撤销令牌
    static async batchRevokeTokens(tokenIds) {
        if (!Array.isArray(tokenIds) || tokenIds.length === 0) {
            return 0;
        }

        const sql = `
            UPDATE tokens 
            SET is_revoked = 1, updated_at = NOW()
            WHERE id IN (${tokenIds.map(() => '?').join(',')}) AND is_revoked = 0
        `;

        try {
            const [result] = await db.execute(sql, tokenIds);
            return result.affectedRows;
        } catch (error) {
            logger.error('批量撤销令牌失败', { error });
            throw error;
        }
    }

    // 检查会话对应的令牌是否全部有效
    static async checkSessionTokensValid(sessionId) {
        const sql = `
            SELECT 
                COUNT(*) as total_tokens,
                COUNT(CASE WHEN is_revoked = 0 AND expires_at > NOW() THEN 1 END) as valid_tokens
            FROM tokens 
            WHERE session_id = ?
        `;

        try {
            const [rows] = await db.execute(sql, [sessionId]);
            const stats = rows[0];
            return {
                has_tokens: stats.total_tokens > 0,
                all_valid: stats.total_tokens > 0 && stats.valid_tokens === stats.total_tokens,
                any_valid: stats.valid_tokens > 0,
                token_count: stats.total_tokens,
                valid_count: stats.valid_tokens
            };
        } catch (error) {
            logger.error('检查会话令牌有效性失败', { error });
            throw error;
        }
    }

    // 撤销除指定会话外的所有用户令牌
    static async revokeOtherSessionTokens(userId, keepSessionId) {
        const sql = `
            UPDATE tokens 
            SET is_revoked = 1, updated_at = NOW()
            WHERE user_id = ? AND session_id != ? AND is_revoked = 0
        `;

        try {
            const [result] = await db.execute(sql, [userId, keepSessionId]);
            return result.affectedRows;
        } catch (error) {
            logger.error('撤销其他会话令牌失败', { error });
            throw error;
        }
    }

    // 更新令牌最后活动时间
    static async updateLastActiveTime(token) {
        const sql = `
            UPDATE tokens 
            SET last_active_time = NOW(), updated_at = NOW()
            WHERE token = ? AND is_revoked = 0
        `;

        try {
            const [result] = await db.execute(sql, [token]);
            return result.affectedRows;
        } catch (error) {
            logger.error('更新令牌最后活动时间失败', { error });
            throw error;
        }
    }
}

module.exports = TokenModel;